package com.ztesoft.sca.dao;

import com.ztesoft.sca.model.ScaOrderResult;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

/**
 * @Author: huang.xizi
 * @Date: 2018/5/30 16:50
 */
@Repository
public class AudioResultDaoImpl implements AudioResultDao {

    @Autowired
    JdbcTemplate jdbcTemplate;


    @Override
    public ScaOrderResult getAudioResultByOrderId(Long orderId) throws Exception {
        StringBuffer sql =new StringBuffer();
        sql.append("SELECT " )
                .append(" CASE  WHEN C.ID IS NOT NULL THEN 'N' ELSE 'Y' END AS isBookStandard, ")
                .append(" CASE  WHEN C.ID IS NOT NULL THEN C.SCA_BOOK_DESC ELSE '符合预约规范'  END AS scaBookDesc, ")
                .append(" CASE  WHEN D.ID IS NOT NULL THEN 'N' ELSE 'Y' END AS handupStandard, ")
                .append(" CASE  WHEN D.ID IS NOT NULL THEN D.SCA_HANDUP_DESC ELSE '符合挂起规范' END AS scaHandupDesc, ")
                .append(" OO.ORDER_ID AS orderId " )
                .append("  FROM (SELECT ? AS ORDER_ID FROM DUAL) OO" )
                .append("  LEFT JOIN (SELECT SAR.RESULT_ID AS ID," )
                .append("                    SAR.IS_BOOK," )
                .append("                    SAR.SCA_BOOK_DESC," )
                .append("                    B.ORDER_ID" )
                .append("               FROM SCA_AUDIO_RESULT SAR" )
                .append("               JOIN (SELECT MAX(A.RESULT_ID) ID, B.ORDER_ID" )
                .append("                      FROM SCA_AUDIO_RESULT A" )
                .append("                      JOIN SCA_AUDIO_SOURCE B" )
                .append("                        ON A.FILE_ID = B.FILE_ID" )
                .append("                     WHERE  B.ORDER_ID = ? " )
                .append("                     AND A.BOOK_FLAG = 'Y'" )
                .append("                     AND IS_BOOK = 'N'" )
                .append("                     GROUP BY B.ORDER_ID) B" )
                .append("                 ON SAR.RESULT_ID = B.ID) C" )
                .append("    ON C.ORDER_ID = OO.ORDER_ID")
                .append("  LEFT JOIN (SELECT SAR.RESULT_ID AS ID," )
                .append("                    SAR.IS_HANDUP," )
                .append("                    SAR.SCA_HANDUP_DESC," )
                .append("                    B.ORDER_ID" )
                .append("               FROM SCA_AUDIO_RESULT SAR" )
                .append("               JOIN (SELECT MAX(A.RESULT_ID) ID, B.ORDER_ID" )
                .append("                      FROM SCA_AUDIO_RESULT A" )
                .append("                      JOIN SCA_AUDIO_SOURCE B" )
                .append("                        ON A.FILE_ID = B.FILE_ID" )
                .append("                     WHERE  B.ORDER_ID = ? " )
                .append("                     AND  A.HANDUP_FLAG = 'Y'" )
                .append("                     AND A.IS_HANDUP = 'N'" )
                .append("                     GROUP BY B.ORDER_ID) B" )
                .append("                 ON SAR.RESULT_ID = B.ID) D" )
                .append("    ON D.ORDER_ID = OO.ORDER_ID");
        ScaOrderResult scaOrderResult = this.jdbcTemplate.queryForObject(sql.toString(), new Object[]{orderId, orderId, orderId}, BeanPropertyRowMapper.newInstance(ScaOrderResult.class));
        return scaOrderResult;
    }
}
